package dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import model.Facility;
import model.FacilityType;
import dao.FacilityDAO;

public class FacilityDAOImpl implements FacilityDAO {

	DataSource dataSource=null;
	Connection con=null;
	PreparedStatement pstmt=null;
	public FacilityDAOImpl()
	{
		try{
			//dataSource=(DataSource) new InitialContext().lookup("java:comp/env/jdbc/fbs");
			//con=dataSource.getConnection();
			con=ConnectionProperties.getConnection();
			}
			catch(Exception ee){
			ee.printStackTrace();
			}
	}
	
	@Override
	public void insertFacility(Facility book) {
		// TODO Auto-generated method stub
		try {
			pstmt=con.prepareStatement("insert into Facility values(?,?,?,?,?,?)");
			pstmt.setString(1, book.getFacilityTypeID());
			pstmt.setString(2, book.getFacilityName());
			pstmt.setInt(3, book.getAvailableRooms());
			pstmt.setInt(4, book.getTotalRooms());
			pstmt.setString(5, book.getDescription());
			pstmt.setInt(6, book.getSeatingCapacity());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
	}

	@Override
	public void updateFacility(Facility book) {
		// TODO Auto-generated method stub
		try {
			pstmt=con.prepareStatement("update facility set Description=? , SeatingCapacity=? where FacilityName=?");

			pstmt.setString(1, book.getDescription());
			pstmt.setInt(2, book.getSeatingCapacity());
			pstmt.setString(3, book.getFacilityName());
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
	}


	@Override
	public void deleteFacility(String FacilityID) throws SQLException{
		// TODO Auto-generated method stub
		try {
			pstmt=con.prepareStatement("delete from Facility where FacilityID=?");
			pstmt.setString(1, FacilityID);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
	}

	@Override
	public Facility findByFacilityName(String FacilityName) {
		// TODO Auto-generated method stub
		Facility facility=null;
		try {
			pstmt=con.prepareStatement("select * from facility f,facilitytype ftype where f.facilitytypeid=ftype.facilitytypeid and f.facilityname=?");
			pstmt.setString(1, FacilityName);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next())
			{
				facility=new Facility();
				facility.setFacilityTypeID(rs.getString("FacilityTypeID"));
				facility.setFacilityName(rs.getString("FacilityName"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				FacilityType fac=new FacilityType();
				fac.setFacilityType(rs.getString("FacilityType"));
				fac.setAvailableRooms(rs.getInt("AvailableRooms"));
				fac.setTotalRooms(rs.getInt("TotalRooms"));
				facility.setFacType(fac);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return facility;
	}


	@Override
	public ArrayList<Facility> findAllFacilities() {
		// TODO Auto-generated method stub
		ArrayList<Facility> facilities=new ArrayList<Facility>();
		try {
			pstmt=con.prepareStatement("select * from Facility");
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				Facility facility=new Facility();
				facility=new Facility();
				facility.setFacilityTypeID(rs.getString("FacilityID"));
				facility.setFacilityName(rs.getString("FacilityName"));
				facility.setAvailableRooms(rs.getInt("AvailableRooms"));
				facility.setTotalRooms(rs.getInt("TotalRooms"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facilities.add(facility);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return facilities;
		
	}

	@Override
	public ArrayList<String> findFacilityNames() {
		// TODO Auto-generated method stub
		ArrayList<String> facilities=new ArrayList<String>();
		try {
			pstmt=con.prepareStatement("select FacilityName from facility");
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				facilities.add(rs.getString("FacilityName"));
				System.out.println(rs.getString("FacilityName"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return facilities;
	}

	@Override
	public ArrayList<Facility> findEverything() throws SQLException {
		// TODO Auto-generated method stub
		ArrayList<Facility> facilities=new ArrayList<Facility>();
		try {
			pstmt=con.prepareStatement("select * from facility,facilitytype where facility.facilitytypeid=facilitytype.facilitytypeid;");
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				Facility facility=new Facility();				
				facility.setFacilityName(rs.getString("FacilityName"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facility.setFacilityTypeID(rs.getString("FacilityTypeID"));
				
				FacilityType fac=new FacilityType();
				fac.setFacilityType(rs.getString("FacilityType"));
				fac.setAvailableRooms(rs.getInt("AvailableRooms"));
				fac.setTotalRooms(rs.getInt("TotalRooms"));
				facility.setFacType(fac);
				facilities.add(facility);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return facilities;		
	}

}
